package com.ep.thirdPlatformProject.oa.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.ep.thirdPlatformProject.oa.entity.vo.EcologyCommonEmpInfoVO;
import com.ep.thirdPlatformProject.oa.entity.dto.EcologyEfficiencyRankDTO;
import com.ep.thirdPlatformProject.oa.entity.dto.EcologyFinishedWorkflowDTO;
import com.ep.thirdPlatformProject.oa.entity.vo.EcologyTodoWorkflowVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


import java.util.List;

@Mapper
@DS("oa_db")
public interface WorkflowHourMapper {

    @Select({"<script>" +
            " SELECT " +
            "    detail.*, " +
            "    CAST(FLOOR(detail.costTotalTime / 24) AS VARCHAR(10)) + '天 ' + " +
            "    CAST(FLOOR(cast( detail.costTotalTime as int) % 24) AS VARCHAR(10)) + '小时 ' + " +
            "    CAST(cast((detail.reduceTime - FLOOR(detail.reduceTime)) * 60 as decimal(10,0)) AS VARCHAR(20)) + '分钟' AS costTotalTimeStr," +
            "    CAST(FLOOR(detail.reduceTime / 24) AS VARCHAR(10)) + '天 ' + " +
            "    CAST(FLOOR(cast( detail.reduceTime as int) % 24) AS VARCHAR(10)) + '小时 ' + " +
            "    CAST(cast((detail.reduceTime - FLOOR(detail.reduceTime)) * 60 as decimal(10,0)) AS VARCHAR(20)) + '分钟' AS reduceTotalTimeStr" +
            " from ( " +
            " SELECT c.workcode as empNo ,c.lastname as empName,a.*,b.workflowname as flowName from " +
            " (select " +
            "  workflow_currentoperator.workflowid, " +
            "  workflow_requestbase.requestmark as requestMark, " +
            "  workflow_requestbase.requestnamenew as requestName, " +
            "  workflow_currentoperator.nodeid, " +
            "  workflow_currentoperator.userid ," +
            "  workflow_currentoperator.requestid," +
            "  convert(datetime, receivedate + ' ' + receivetime) as receiveTime, " +
            "  convert(datetime, operatedate + ' ' + operatetime) as operationTime, " +
            "  ( select nodename from workflow_nodebase where id = workflow_currentoperator.nodeid) as nodeName, " +
            "  cast( 24*convert(float, convert(datetime, operatedate + ' ' + operatetime) -convert(datetime, receivedate + ' ' + receivetime)) as decimal(10,3)) as costTotalTime, " +
            "    cast((   " +
            "  24* convert(float, convert(datetime, operatedate + ' ' + operatetime) -convert(datetime, receivedate + ' ' + receivetime))   " +
            "   - convert(float, isnull( dbo.GetNonWorkHoursBetweenDates(  " +
            "       convert(datetime, receivedate + ' ' + receivetime),  " +
            "        convert(datetime, case isremark   " +
            "      when '2' then   " +
            "      case operatedate  " +
            "      when '' then convert(char(10), getdate(), 20) else operatedate end  " +
            "      when null then isnull (operatedate, convert(char(10), getdate(), 20)) else convert(char(10), getdate(), 20) end  " +
            "      +' '+  " +
            "      case isremark   " +
            "      when '2' then case operatetime  " +
            "      when '' then convert(char(10), getdate(), 108) else operatetime end   " +
            "      when null then isnull(operatetime, convert(char(10), getdate(), 108)) else  convert(char(10), getdate(), 108) end )  " +
            "        ) ,0))) as decimal(10,3))  as reduceTime " +
            " from workflow_currentoperator, workflow_requestbase " +
            " where " +
            "  workflow_requestbase.requestid = workflow_currentoperator.requestid " +
            "  and (workflow_requestbase.status is not null and workflow_requestbase.status != '') " +
            "  and workflow_currentoperator.isremark != 0 and isreject is NULL  " +
            " and workflow_currentoperator.receivedate <![CDATA[ >= ]]>" +
            " <choose> "+
            " <when test='startDate==null or startDate.length==0'> DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0)  </when> " +
            " <otherwise>  #{startDate} </otherwise>"+
            " </choose> "+
            "  and workflow_currentoperator.receivedate <![CDATA[ <= ]]> " +
            " <choose> "+
            " <when test='endDate==null or endDate.length==0'> DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) </when> " +
            " <otherwise>  #{endDate} </otherwise>"+
            " </choose> "+
            "  and viewtype = -2  and operatedate !='' " +
            "  and isremark != 4 " +
            "  and userid in  " +
            "   <foreach collection='userIds' item='userId' open='(' separator=',' close=')'>" +
            "    #{userId}   " +
            "   </foreach>  " +
            "  and preisremark = '0' " +
            "  and exists ( select 1 from workflow_flownode " +
            "  where nodeid = workflow_currentoperator.nodeid and  workflowid = workflow_currentoperator.workflowid and nodetype != 3) " +
            "  )a " +
            "  left join workflow_base b on a.workflowid = b.id " +
            "  left join HrmResource c on c.id =a.userid " +
            "  inner join workflow_requestoperatelog d on a.requestid =d.requestid and a.userid = d.operatorid and d.nodeid = a.nodeid where d.operatecode = 1 " +
            ") detail " +
            " where nodeName not like '%发起人' " +
            "</script>"})
    List<EcologyEfficiencyRankDTO> selectWorkflowListVO(@Param("userIds") List<String> userIds, @Param("startDate") String startDate, @Param("endDate") String endDate);


    @Select({"<script>" +
            " select userid as userId,b.lastname as empName,b.workcode as empNo,a.workflowcount as workflowCount " +
            " from ( " +
            " select userid,count(distinct requestid) workflowcount from workflow_currentoperator " +
            " where  workflowtype > 0 and isremark in ('0','1','5','9','7') and islasttimes=1 and  usertype='0'" +
            "  and workflow_currentoperator.receivedate <![CDATA[ >= ]]> #{startDate} " +
            "  and workflow_currentoperator.receivedate <![CDATA[ <= ]]> #{endDate}" +
            "  and isreject is NULL " +
            "  and userid in  " +
            "   <foreach collection='userIds' item='userId' open='(' separator=',' close=')'>" +
            "    #{userId}   " +
            "   </foreach>  " +
            " group by userid " +
            " ) a inner join HrmResource b  on a.userid=b.id " +
            "</script>"})
    List<EcologyTodoWorkflowVO> selectTodoWorkflowCount(@Param("userIds") List<String> userIds, @Param("startDate") String startDate, @Param("endDate") String endDate);


    @Select({"<script>" +
            "SELECT c.workcode as empNo ,c.lastname as empName,c.id as userId, 0 as workflowCount " +
            " from( " +
            " select workflow_currentoperator.workflowid,workflow_currentoperator.nodeid,workflow_currentoperator.userid ,workflow_currentoperator.requestid," +
            " ( select nodename from workflow_nodebase where id = workflow_currentoperator.nodeid) as nodeName" +
            " from workflow_currentoperator,workflow_requestbase" +
            " where workflow_requestbase.requestid = workflow_currentoperator.requestid " +
            " and (workflow_requestbase.status is not null and workflow_requestbase.status != '') " +
            " and workflow_currentoperator.isremark != 0 " +
            " and isreject is NULL "+
            " and viewtype = -2 " +
            " and operatedate != '' " +
            " and isremark != 4 " +
            " and userid  in " +
            "   <foreach collection='userIds' item='userId' open='(' separator=',' close=')'>" +
            "    #{userId}   " +
            "   </foreach>  " +
            " and workflow_currentoperator.receivedate <![CDATA[ >= ]]> #{startDate} " +
            " and workflow_currentoperator.receivedate <![CDATA[ <= ]]> #{endDate} " +
            " and preisremark = '0' " +
            " and exists (select 1 from  workflow_flownode where  nodeid = workflow_currentoperator.nodeid " +
            "    and workflowid = workflow_currentoperator.workflowid " +
            "    and nodetype != 3) " +
            " )a " +
            " left join workflow_base b on a.workflowid = b.id " +
            " left join HrmResource c on c.id = a.userid " +
            " inner join workflow_requestoperatelog d on a.requestid = d.requestid and a.userid = d.operatorid  and d.nodeid = a.nodeid" +
            " where d.operatecode = 1  and a.nodeName  not like '%发起人'" +
            "</script>"})
    List<EcologyFinishedWorkflowDTO> selectFinishedWorkflowCount(@Param("userIds") List<String> userIds, @Param("startDate") String startDate, @Param("endDate") String endDate);


    @Select({"<script>" +
            " select id as userId,workcode as empNo, lastname as empName from HrmResource where" +
            "  id in  " +
            "   <foreach collection='userIds' item='userId' open='(' separator=',' close=')'>" +
            "    #{userId}   " +
            "   </foreach>  " +
            "</script>"})
    List<EcologyCommonEmpInfoVO> selectEmpInfoByUserIds(@Param("userIds") List<String> userIds);
}
